<?php

/* 查看未使用索引

  DECLARE  @dbid INT

SELECT @dbid = DB_ID(DB_NAME())

SELECT
    OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
    INDEXNAME = I.NAME,
    I.INDEX_ID
FROM
    SYS.INDEXES I
JOIN
    SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
WHERE
    OBJECTPROPERTY(O.OBJECT_ID, 'IsUserTable') = 1
    AND I.INDEX_ID NOT IN
       (SELECT S.INDEX_ID
        FROM SYS.DM_DB_INDEX_USAGE_STATS S
        WHERE S.OBJECT_ID = I.OBJECT_ID
        AND I.INDEX_ID = S.INDEX_ID
        AND DATABASE_ID = @dbid)
ORDER BY
    OBJECTNAME, I.INDEX_ID, INDEXNAME ASC

 */

/* 慢sql查询

 SELECT
(total_elapsed_time / execution_count)/1000 N'平均时间ms'
,total_elapsed_time/1000 N'总花费时间ms'
,total_worker_time/1000 N'所用的CPU总时间ms'
,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数'
,total_logical_reads N'逻辑读取总次数'
,total_logical_writes N'逻辑写入总次数'
,execution_count N'执行次数'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'执行语句'
,creation_time N'语句编译时间'
,last_execution_time N'上次执行时间'
FROM
sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
WHERE
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like '%fetch%'
ORDER BY
-- last_execution_time DESC
 total_elapsed_time / execution_count DESC;

 */

/* 统计表大小

exec sp_MSForEachTable
@precommand=N'CREATE table ###(
表名 sysname,
记录数 int,
保留空间 Nvarchar(10),
使用空间 varchar(10),
索引使用空间 varchar(10),
未用空间 varchar(10))',
@command1=N'insert ### exec sp_spaceused ''?''',
@postcommand=N'select * from ### order by 记录数 '

 */
